/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pos.server.dac.dao;

import com.google.gwt.user.client.Window;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import java.sql.*;
import pos.client.dac.dao.OperazioneFinanziariaDAO;
import pos.domain.OperazioneFinanziaria;
import pos.domain.Utente;
import pos.server.dac.ConnectionManager;
import pos.server.dac.exceptions.EccezioneCRUD;

/**
 *
 * @author Alessio
 */
public class OperazioneFinanziariaDAOImpl extends RemoteServiceServlet implements OperazioneFinanziariaDAO
{

    private String SELECT_SQL = "SELECT * FROM operazionefinanziaria WHERE id_of = ?";
    private String INSERT_SQL = "INSERT INTO operazionefinanziaria (categoria, nome, data, somma) VALUES (?,?,?,?) RETURNING id_of";
    private String INSERT_SQL2 ="INSERT INTO u_fo (id_of, utente) VALUES (?,?)";
    private String UPDATE_SQL = "UPDATE operazionefinanziaria SET categoria = ?, nome = ?, data=?, somma=? WHERE id_of = ?";
    private String DELETE_SQL = "DELETE FROM operazionefinanziaria WHERE id_of = ?";
    private String DELETE_SQL2 = "DELETE FROM u_fo WHERE id_of = ?";
    
    @Override
    public void leggiDatidaDB(OperazioneFinanziaria obj) throws EccezioneCRUD
    {
        try {
            Connection con = null;
            PreparedStatement pstmt = null;
            con = ConnectionManager.getConnection();
            pstmt = con.prepareStatement(SELECT_SQL);
            pstmt.setInt(1, obj.getId_of());
            ResultSet result = pstmt.executeQuery();
            if(result.next()){
                obj.setCategoria(result.getString("categoria"));
                obj.setNome(result.getString("nome"));
                obj.setData(result.getTimestamp("data").getTime());
                obj.setSomma(result.getInt("somma"));
            }
            pstmt.close();
            con.close(); //si assume la modalita’ autocommit
        } catch (SQLException ex) {
            throw new EccezioneCRUD(ex.getMessage());
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.getCause());
        }
    }

    @Override
    public OperazioneFinanziaria inserisciDatisuDB(OperazioneFinanziaria obj, Utente u) throws EccezioneCRUD
    {
       System.out.println("\n In inserisciDatiSuDB in DAO");     
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = ConnectionManager.getConnection();
            pstmt = con.prepareStatement(INSERT_SQL);
            pstmt.setString(1, obj.getCategoria());
            pstmt.setString(2, obj.getNome());
            pstmt.setTimestamp(3, new Timestamp(obj.getData()));
            pstmt.setDouble(4, obj.getSomma());
            con.setAutoCommit(false);
             
            //ResultSet rs = pstmt.executeQuery("SELECT currval(operazionefinanziaria_id_of_seq)");
     
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            obj.setId_of(rs.getInt(1));
            
            pstmt = con.prepareStatement(INSERT_SQL2);
            pstmt.setInt(1, obj.getId_of());
            pstmt.setString(2, u.getLogin());
            
            pstmt.executeUpdate();
            con.commit();
            con.setAutoCommit(true);
        } catch (SQLException ex) {
            throw new EccezioneCRUD(ex.getMessage());
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.getCause());
        } finally{
            if(con != null){
                try {
                    con.setAutoCommit(true);
                    if(pstmt != null)
                        pstmt.close();
                    con.close();
                } catch (SQLException ex) {
                    throw new EccezioneCRUD(ex.getMessage());
                }
            }
        }
        return obj;
    }
        
    @Override
    public void aggiornaDatisuDB(OperazioneFinanziaria obj) throws EccezioneCRUD
    {
        try {
            Connection con = null;
            PreparedStatement pstmt = null;
            con = ConnectionManager.getConnection();
            pstmt = con.prepareStatement(UPDATE_SQL);
            pstmt.setInt(5, obj.getId_of());
            pstmt.setString(1, obj.getCategoria());
            pstmt.setString(2, obj.getNome());
            pstmt.setTimestamp(3, new Timestamp(obj.getData()));
            pstmt.setDouble(4, obj.getSomma());
            pstmt.executeUpdate();
            pstmt.close();
            con.close(); //si assume la modalita’ autocommit
        } catch (SQLException ex) {
            throw new EccezioneCRUD(ex.getMessage());
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.getCause());
        }
    }

    @Override
    public void cancellaDatidaDB(OperazioneFinanziaria obj) throws EccezioneCRUD
    {
        try {
            Connection con = null;
            PreparedStatement pstmt = null;
            con = ConnectionManager.getConnection();
            pstmt = con.prepareStatement(DELETE_SQL);
            pstmt.setInt(1, obj.getId_of());
            con.setAutoCommit(false);
            pstmt.executeUpdate();
            
            con.prepareStatement(DELETE_SQL2);
            pstmt.setInt(1, obj.getId_of());
            pstmt.executeUpdate();
            con.commit();
            pstmt.close();
            con.close(); //si assume la modalita’ autocommit
        } catch (SQLException ex) {
            throw new EccezioneCRUD(ex.getMessage());
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.getCause());
        }
    }

}
